import tkinter as tk
from tkinter import ttk, messagebox, scrolledtext
import threading
import time
from typing import Dict, Any, List
from .client import DatabaseClient
from ..server.protocol import MessageType
from src.compiler.compiler import SQLCompiler
from src.execution.planner import QueryPlanner
from src.catalog.table_manager import *
import os
class DatabaseGUI:
    """数据库图形化客户端"""
    
    def __init__(self):
        self.client = DatabaseClient()
        self.root = tk.Tk()
        self.root.title("RBDB 数据库客户端")
        self.root.geometry("900x800")  # 增加高度以容纳错误显示区域
        self.root.resizable(True, True)

        self.compiler = SQLCompiler()
        self.planner = QueryPlanner()

        self.db_dir = os.path.abspath(os.path.dirname(__file__))
        self.db_file_path = os.path.join(self.db_dir, "test_crud.db")

        # 创建数据管理器和表管理器
        self.dm = DataManager.create(self.db_file_path, 1024 * 1024)  # 1MB内存
        self.booter = Booter.create(self.db_file_path)
        self.tbm = TableManager(self.dm, self.booter)

        # 状态变量
        self.connected = False
        self.logged_in = False

        self.current_xid=0

        # 添加错误和消息存储
        self.last_error = None
        self.last_result = None
        
        self.setup_ui()
        
    def setup_ui(self):
        """设置用户界面"""
        # 创建主框架
        main_frame = ttk.Frame(self.root, padding="10")
        main_frame.grid(row=0, column=0, sticky=(tk.W, tk.E, tk.N, tk.S))
        
        # 配置网格权重
        self.root.columnconfigure(0, weight=1)
        self.root.rowconfigure(0, weight=1)
        main_frame.columnconfigure(1, weight=1)
        main_frame.rowconfigure(4, weight=1)  # 结果显示区域
        main_frame.rowconfigure(5, weight=0)  # 错误显示区域
        
        # 连接区域
        self.setup_connection_frame(main_frame)
        
        # 登录区域
        self.setup_login_frame(main_frame)
        
        # SQL输入区域
        self.setup_sql_frame(main_frame)
        
        # 结果显示区域
        self.setup_result_frame(main_frame)
        
        # 错误显示区域 - 新增
        self.setup_error_frame(main_frame)
        
        # 状态栏
        self.setup_status_bar(main_frame)
        
    def setup_connection_frame(self, parent):
        """设置连接框架"""
        conn_frame = ttk.LabelFrame(parent, text="服务器连接", padding="5")
        conn_frame.grid(row=0, column=0, columnspan=2, sticky=(tk.W, tk.E), pady=(0, 10))
        conn_frame.columnconfigure(1, weight=1)
        
        # 主机地址
        ttk.Label(conn_frame, text="主机:").grid(row=0, column=0, sticky=tk.W, padx=(0, 5))
        self.host_var = tk.StringVar(value="localhost")
        ttk.Entry(conn_frame, textvariable=self.host_var, width=20).grid(row=0, column=1, sticky=(tk.W, tk.E), padx=(0, 10))
        
        # 端口
        ttk.Label(conn_frame, text="端口:").grid(row=0, column=2, sticky=tk.W, padx=(10, 5))
        self.port_var = tk.StringVar(value="8888")
        ttk.Entry(conn_frame, textvariable=self.port_var, width=10).grid(row=0, column=3, sticky=tk.W, padx=(0, 10))
        
        # 连接按钮
        self.connect_btn = ttk.Button(conn_frame, text="连接", command=self.toggle_connection)
        self.connect_btn.grid(row=0, column=4, sticky=tk.W)
        
    def setup_login_frame(self, parent):
        """设置登录框架"""
        login_frame = ttk.LabelFrame(parent, text="用户登录", padding="5")
        login_frame.grid(row=1, column=0, columnspan=2, sticky=(tk.W, tk.E), pady=(0, 10))
        login_frame.columnconfigure(1, weight=1)
        
        # 用户名
        ttk.Label(login_frame, text="用户名:").grid(row=0, column=0, sticky=tk.W, padx=(0, 5))
        self.username_var = tk.StringVar(value="admin")
        ttk.Entry(login_frame, textvariable=self.username_var, width=20).grid(row=0, column=1, sticky=(tk.W, tk.E), padx=(0, 10))
        
        # 密码
        ttk.Label(login_frame, text="密码:").grid(row=0, column=2, sticky=tk.W, padx=(10, 5))
        self.password_var = tk.StringVar(value="admin123")
        password_entry = ttk.Entry(login_frame, textvariable=self.password_var, show="*", width=20)
        password_entry.grid(row=0, column=3, sticky=(tk.W, tk.E), padx=(0, 10))
        
        # 登录按钮
        self.login_btn = ttk.Button(login_frame, text="登录", command=self.toggle_login, state="disabled")
        self.login_btn.grid(row=0, column=4, sticky=tk.W)
        
        # 用户信息显示
        self.user_info_var = tk.StringVar(value="未登录")
        ttk.Label(login_frame, textvariable=self.user_info_var, foreground="blue").grid(row=1, column=0, columnspan=5, sticky=tk.W, pady=(5, 0))
        
    def setup_sql_frame(self, parent):
        """设置SQL输入框架"""
        sql_frame = ttk.LabelFrame(parent, text="SQL 查询", padding="5")
        sql_frame.grid(row=2, column=0, columnspan=2, sticky=(tk.W, tk.E), pady=(0, 10))
        sql_frame.columnconfigure(0, weight=1)
        
        # SQL输入框
        self.sql_text = scrolledtext.ScrolledText(sql_frame, height=4, width=80)
        self.sql_text.grid(row=0, column=0, sticky=(tk.W, tk.E), pady=(0, 5))
        self.sql_text.insert(tk.END, "SELECT * FROM users;")
        
        # 按钮框架
        btn_frame = ttk.Frame(sql_frame)
        btn_frame.grid(row=1, column=0, sticky=(tk.W, tk.E))
        
        # 执行按钮
        self.execute_btn = ttk.Button(btn_frame, text="执行 SQL", command=self.execute_sql)
        self.execute_btn.pack(side=tk.LEFT, padx=(0, 10))
        
        # 清空按钮
        ttk.Button(btn_frame, text="清空", command=lambda: self.sql_text.delete(1.0, tk.END)).pack(side=tk.LEFT, padx=(0, 10))
        
        # 示例SQL按钮
        ttk.Button(btn_frame, text="示例查询", command=self.insert_sample_sql).pack(side=tk.LEFT)
        
    def setup_result_frame(self, parent):
        """设置结果显示框架"""
        result_frame = ttk.LabelFrame(parent, text="查询结果", padding="5")
        result_frame.grid(row=3, column=0, columnspan=2, sticky=(tk.W, tk.E, tk.N, tk.S), pady=(0, 5))
        result_frame.columnconfigure(0, weight=1)
        result_frame.rowconfigure(0, weight=1)
        
        # 创建Treeview用于显示表格数据
        self.result_tree = ttk.Treeview(result_frame)
        self.result_tree.grid(row=0, column=0, sticky=(tk.W, tk.E, tk.N, tk.S))
        
        # 添加滚动条
        v_scrollbar = ttk.Scrollbar(result_frame, orient="vertical", command=self.result_tree.yview)
        v_scrollbar.grid(row=0, column=1, sticky=(tk.N, tk.S))
        self.result_tree.configure(yscrollcommand=v_scrollbar.set)
        
        h_scrollbar = ttk.Scrollbar(result_frame, orient="horizontal", command=self.result_tree.xview)
        h_scrollbar.grid(row=1, column=0, sticky=(tk.W, tk.E))
        self.result_tree.configure(xscrollcommand=h_scrollbar.set)
        
        # 结果信息标签
        self.result_info_var = tk.StringVar(value="准备就绪")
        ttk.Label(result_frame, textvariable=self.result_info_var, foreground="green").grid(row=2, column=0, columnspan=2, sticky=tk.W, pady=(5, 0))
    
    def setup_error_frame(self, parent):
        """设置错误显示框架 - 新增方法"""
        error_frame = ttk.LabelFrame(parent, text="消息和错误信息", padding="5")
        error_frame.grid(row=4, column=0, columnspan=2, sticky=(tk.W, tk.E), pady=(0, 10))
        error_frame.columnconfigure(0, weight=1)
        error_frame.rowconfigure(0, weight=1)
        
        # 创建文本框显示错误和消息
        self.error_text = scrolledtext.ScrolledText(error_frame, height=6, wrap=tk.WORD)
        self.error_text.grid(row=0, column=0, sticky=(tk.W, tk.E, tk.N, tk.S))
        
        # 配置文本样式
        self.error_text.tag_configure("error", foreground="red")
        self.error_text.tag_configure("success", foreground="green")
        self.error_text.tag_configure("info", foreground="blue")
        self.error_text.tag_configure("warning", foreground="orange")
        
        # 清空按钮
        clear_btn = ttk.Button(error_frame, text="清空消息", command=self.clear_messages)
        clear_btn.grid(row=1, column=0, sticky=tk.W, pady=(5, 0))
    
    def setup_status_bar(self, parent):
        """设置状态栏"""
        status_frame = ttk.Frame(parent)
        status_frame.grid(row=5, column=0, columnspan=2, sticky=(tk.W, tk.E), pady=(10, 0))  # 更新行号
        status_frame.columnconfigure(1, weight=1)
        
        # 状态标签
        self.status_var = tk.StringVar(value="未连接")
        ttk.Label(status_frame, textvariable=self.status_var).grid(row=0, column=0, sticky=tk.W)
        
        # 进度条（用于显示操作进度）
        self.progress = ttk.Progressbar(status_frame, mode='indeterminate')
        self.progress.grid(row=0, column=1, sticky=(tk.W, tk.E), padx=(10, 0))
        
    def toggle_connection(self):
        """切换连接状态"""
        if not self.connected:
            self.connect_to_server()
        else:
            self.disconnect_from_server()
            
    def connect_to_server(self):
        """连接到服务器"""
        host = self.host_var.get().strip()
        try:
            port = int(self.port_var.get().strip())
        except ValueError:
            messagebox.showerror("错误", "端口必须是数字")
            return
            
        self.progress.start()
        self.status_var.set("正在连接...")
        
        def connect_thread():
            self.client.host = host
            self.client.port = port
            
            if self.client.connect():
                self.root.after(0, self.on_connected)
            else:
                self.root.after(0, self.on_connect_failed)
                
        threading.Thread(target=connect_thread, daemon=True).start()
        
    def on_connected(self):
        """连接成功回调"""
        self.progress.stop()
        self.connected = True
        self.connect_btn.config(text="断开")
        self.login_btn.config(state="normal")
        self.status_var.set(f"已连接到 {self.host_var.get()}:{self.port_var.get()}")
        
    def on_connect_failed(self):
        """连接失败回调"""
        self.progress.stop()
        self.status_var.set("连接失败")
        self.show_error("连接错误", "无法连接到数据库服务器")
        
    def disconnect_from_server(self):
        """断开服务器连接"""
        if self.logged_in:
            self.client.logout()
            self.logged_in = False
            self.login_btn.config(text="登录")
            self.execute_btn.config(state="disabled")
            self.user_info_var.set("未登录")
            
        self.client.disconnect()
        self.connected = False
        self.connect_btn.config(text="连接")
        self.login_btn.config(state="disabled")
        self.status_var.set("未连接")
        
    def toggle_login(self):
        """切换登录状态"""
        if not self.logged_in:
            self.login_to_server()
        else:
            self.logout_from_server()
            
    def login_to_server(self):
        """登录到服务器"""
        username = self.username_var.get().strip()
        password = self.password_var.get().strip()
        
        if not username or not password:
            messagebox.showerror("错误", "用户名和密码不能为空")
            return
            
        self.progress.start()
        self.status_var.set("正在登录...")
        
        def login_thread():
            if self.client.login(username, password):
                self.root.after(0, self.on_login_success)
            else:
                self.root.after(0, self.on_login_failed)
                
        threading.Thread(target=login_thread, daemon=True).start()
        
    def on_login_success(self):
        """登录成功回调"""
        self.progress.stop()
        self.logged_in = True
        self.login_btn.config(text="登出")
        self.execute_btn.config(state="normal")
        
        if self.client.user_info:
            username = self.client.user_info.get('username', '未知用户')
            permissions = ', '.join(self.client.user_info.get('permissions', []))
            self.user_info_var.set(f"用户: {username} | 权限: {permissions}")
            
        self.status_var.set("登录成功")
        
    def on_login_failed(self):
        """登录失败回调"""
        self.progress.stop()
        self.status_var.set("登录失败")
        self.show_error("登录错误", "用户名或密码错误")
        
    def logout_from_server(self):
        """从服务器登出"""
        self.client.logout()
        self.logged_in = False
        self.login_btn.config(text="登录")
        self.execute_btn.config(state="disabled")
        self.user_info_var.set("未登录")
        self.status_var.set("已登出")

    def execute_sql(self):
        sql = self.sql_text.get(1.0, tk.END).strip()
        if not sql:
            self.show_error("输入错误", "请输入SQL语句")
            return

        self.progress.start()
        self.status_var.set("正在执行SQL...")
        self.result_info_var.set("执行中...")

        def execute_thread():
            self.root.after(0, self.clear_results)
            try:
                # 第1步：编译SQL，得到四元式
                quads, compile_error = self.compiler.compile(sql)
                print("quads:", quads)
                if compile_error:
                    # 如果编译出错，直接显示错误
                    self.root.after(0, lambda: self.on_sql_executed(False, None, compile_error))
                    return

                # 第2步：使用Planner翻译并执行四元式
                # 注意：我们在这里传入了 self.current_xid
                result_data = self.planner.plan_and_execute(self.tbm, self.current_xid, quads)
                print("result_data:", result_data)

                # 第3步：格式化结果并更新UI
                formatted_result = self.format_result(result_data)
                print("formatted_result:", formatted_result)
                self.root.after(0, lambda: self.on_sql_executed(True, formatted_result, None))

            except Exception as e:
                import traceback
                traceback.print_exc()
                # 捕获计划或执行期间的任何错误
                error_message = f"执行失败: {e}"
                self.root.after(0, lambda err_msg=error_message: self.on_sql_executed(False, None, err_msg))

        threading.Thread(target=execute_thread, daemon=True).start()

    def format_result(self, result_from_tm):
        """将 TableManager 的输出格式化为GUI可以显示的数据"""
        # TableManager 的返回结果是 bytes
        if isinstance(result_from_tm, bytes):
            # 这是 CREATE, INSERT, DELETE, UPDATE 的结果
            return {"message": result_from_tm.decode('utf-8')}

        if isinstance(result_from_tm, list):
            # 检查列表是否为空
            if not result_from_tm:
                return {"columns": [], "rows": [], "row_count": 0}

            # 检查列表内容是否是Tuple对象
            if not hasattr(result_from_tm[0], 'to_dict'):
                # 如果不是Tuple对象（可能是未来的其他类型），可以保留旧逻辑或报错
                # 为了安全，我们假设它必须是可转换为dict的对象
                raise TypeError("Result data from execution engine is not in expected format (list of Tuple objects)")

            # 将Tuple对象列表转换为字典列表
            dict_list = [t.to_dict() for t in result_from_tm]

            # 现在可以使用字典的 .keys() 方法了
            columns = list(dict_list[0].keys())
            rows = [list(d.values()) for d in dict_list]

            return {
                "columns": columns,
                "rows": dict_list,  # <--- 直接返回 dict_list
                "row_count": len(dict_list)
            }

            # 对于其他未知类型，返回一个通用消息
        return {"message": f"执行完成，但返回了未知的数据类型: {type(result_from_tm)}"}

    def execute_sql_with_result(self, sql: str):
        """执行SQL并返回详细结果 - 新增方法"""
        if not self.client.session_id:
            return False, None, "请先登录"
        
        try:
            # 重写客户端的execute_sql方法以支持返回结果
            from ..server.protocol import Protocol, MessageType
            import time
            
            message = Protocol.encode_message(MessageType.SQL_QUERY, {"sql": sql}, self.client.session_id)
            self.client.socket.send(message)
            
            # 等待响应并解析结果
            time.sleep(1)  # 简单等待，实际应该用更好的同步机制
            
            # 这里需要从客户端获取最后的响应结果
            # 由于当前架构限制，我们先返回模拟结果
            return True, self.get_mock_result(sql), None
            
        except Exception as e:
            return False, None, f"执行SQL失败: {str(e)}"
    
    def get_mock_result(self, sql: str):
        """获取模拟结果 - 临时方法"""
        sql_lower = sql.lower().strip()
        
        if sql_lower.startswith('select'):
            return {
                "columns": ["id", "name", "age", "email"],
                "rows": [
                    {"id": 1, "name": "张三", "age": 25, "email": "zhangsan@example.com"},
                    {"id": 2, "name": "李四", "age": 30, "email": "lisi@example.com"},
                    {"id": 3, "name": "王五", "age": 28, "email": "wangwu@example.com"}
                ],
                "row_count": 3,
                "execution_time": "0.05s"
            }
        elif sql_lower.startswith(('insert', 'update', 'delete')):
            return {
                "affected_rows": 1,
                "execution_time": "0.02s",
                "message": "操作成功完成"
            }
        else:
            return {
                "message": "SQL语句执行完成",
                "execution_time": "0.01s"
            }
    
    def on_sql_executed(self, success, result_data, error_msg):
        """SQL执行完成回调 - 修改方法"""
        self.progress.stop()
        
        if success and result_data:
            self.status_var.set("SQL执行完成")
            
            if "columns" in result_data and "rows" in result_data:
                # 查询结果
                self.display_results(result_data["columns"], result_data["rows"])
                self.show_success("查询成功", f"查询完成，共 {result_data.get('row_count', 0)} 行结果，执行时间: {result_data.get('execution_time', 'N/A')}")
            elif "affected_rows" in result_data:
                # DML操作结果
                self.show_success("操作成功", f"影响 {result_data['affected_rows']} 行，执行时间: {result_data.get('execution_time', 'N/A')}")
                self.result_info_var.set(f"操作完成，影响 {result_data['affected_rows']} 行")
            else:
                # 其他操作结果
                self.show_success("执行成功", result_data.get('message', 'SQL语句执行完成'))
                self.result_info_var.set("执行完成")
        else:
            self.status_var.set("SQL执行失败")
            self.result_info_var.set("执行失败")
            self.show_error("SQL执行错误", error_msg or "未知错误")
        
    def display_results(self, columns: List[str], rows: List[Dict[str, Any]]):
        """显示查询结果"""
        # 设置列
        self.result_tree["columns"] = columns
        self.result_tree["show"] = "headings"
        
        # 配置列标题和宽度
        for col in columns:
            self.result_tree.heading(col, text=col)
            self.result_tree.column(col, width=100, minwidth=50)
            
        # 插入数据行
        for i, row in enumerate(rows):
            values = [str(row.get(col, "")) for col in columns]
            self.result_tree.insert("", "end", iid=i, values=values)
            
        # 更新结果信息
        self.result_info_var.set(f"查询完成，共 {len(rows)} 行结果")
        
    def clear_results(self):
        """清空结果显示"""
        for item in self.result_tree.get_children():
            self.result_tree.delete(item)
        
    def show_error(self, title: str, message: str):
        """显示错误信息 - 新增方法"""
        import datetime
        timestamp = datetime.datetime.now().strftime("%H:%M:%S")
        error_text = f"[{timestamp}] 错误 - {title}: {message}\n"
        
        self.error_text.insert(tk.END, error_text, "error")
        self.error_text.see(tk.END)
        
        # 同时更新状态栏
        self.status_var.set(f"错误: {title}")
        
    def show_success(self, title: str, message: str):
        """显示成功信息 - 新增方法"""
        import datetime
        timestamp = datetime.datetime.now().strftime("%H:%M:%S")
        success_text = f"[{timestamp}] 成功 - {title}: {message}\n"
        
        self.error_text.insert(tk.END, success_text, "success")
        self.error_text.see(tk.END)
        
    def show_info(self, title: str, message: str):
        """显示信息 - 新增方法"""
        import datetime
        timestamp = datetime.datetime.now().strftime("%H:%M:%S")
        info_text = f"[{timestamp}] 信息 - {title}: {message}\n"
        
        self.error_text.insert(tk.END, info_text, "info")
        self.error_text.see(tk.END)
        
    def show_warning(self, title: str, message: str):
        """显示警告信息 - 新增方法"""
        import datetime
        timestamp = datetime.datetime.now().strftime("%H:%M:%S")
        warning_text = f"[{timestamp}] 警告 - {title}: {message}\n"
        
        self.error_text.insert(tk.END, warning_text, "warning")
        self.error_text.see(tk.END)
        
    def clear_messages(self):
        """清空消息显示 - 新增方法"""
        self.error_text.delete(1.0, tk.END)
        
    def insert_sample_sql(self):
        """插入示例SQL"""
        samples = [
            "create table user (id INTEGER, name VARCHAR);",
            "INSERT INTO user(id, name) VALUES(1, 'pass');",
            "INSERT INTO user(id, name) VALUES(2, 'passs');",
            "INSERT INTO user(id, name) VALUES(3, 'passss');",
            "SELECT id,name FROM user;",
            "SELECT id,name FROM user WHERE id = 2;",
            "DELETE FROM user WHERE id = 2;",
            "update user set name = 'passpass' WHERE id=2;",
            "DELETE FROM user;",
            "update user set id=2;",
            "SELECT * FROM users;",
            "SELECT * FROM products;",
            "SHOW TABLES;",
            "INSERT INTO users (name, age, email) VALUES ('测试用户', 25, 'test@example.com');",
            "UPDATE users SET age = 26 WHERE name = '张三';",
            "DELETE FROM users WHERE id = 1;"
        ]
        
        # 创建选择对话框
        dialog = tk.Toplevel(self.root)
        dialog.title("选择示例SQL")
        dialog.geometry("400x300")
        dialog.transient(self.root)
        dialog.grab_set()
        
        # 列表框
        listbox = tk.Listbox(dialog, height=10)
        listbox.pack(fill=tk.BOTH, expand=True, padx=10, pady=10)
        
        for sample in samples:
            listbox.insert(tk.END, sample)
            
        # 按钮框架
        btn_frame = ttk.Frame(dialog)
        btn_frame.pack(fill=tk.X, padx=10, pady=(0, 10))
        
        def insert_selected():
            selection = listbox.curselection()
            if selection:
                sql = samples[selection[0]]
                self.sql_text.delete(1.0, tk.END)
                self.sql_text.insert(1.0, sql)
            dialog.destroy()
            
        ttk.Button(btn_frame, text="插入", command=insert_selected).pack(side=tk.RIGHT, padx=(5, 0))
        ttk.Button(btn_frame, text="取消", command=dialog.destroy).pack(side=tk.RIGHT)
        
        # 双击插入
        listbox.bind("<Double-Button-1>", lambda e: insert_selected())
        
    def run(self):
        """运行GUI应用"""
        try:
            self.root.mainloop()
        except KeyboardInterrupt:
            pass
        finally:
            if self.connected:
                self.client.disconnect()

def main():
    """主函数"""
    app = DatabaseGUI()
    app.run()

if __name__ == "__main__":
    main()